﻿--casa giảm

declare @date_end_month nvarchar(100)= (select [MAX_DATE_ENDMONTH] from [DATE_EM_REPORT])
declare @month_count nvarchar(10)
declare @last_month nvarchar(10)= (convert(nvarchar(6),@date_end_month,112))
declare @2_month_ago nvarchar(10)= (convert(nvarchar(6),DATEADD(m,-1,CONVERT(date,@date_end_month)),112))
declare @3_month_ago nvarchar(10)= (convert(nvarchar(6),DATEADD(m,-2,CONVERT(date,@date_end_month)),112))
declare @4_month_ago nvarchar(10)= (convert(nvarchar(6),DATEADD(m,-3,CONVERT(date,@date_end_month)),112))
declare @count int = 0;
print @2_month_ago
while (@count <4)
begin
set @month_count = (convert(nvarchar(6),DATEADD(m,-@count,CONVERT(date,@date_end_month)),112))
exec('
IF EXISTS
(SELECT *
FROM DBO.SYSOBJECTS O
WHERE O.XTYPE IN (''U'') AND O.NAME = ''avgbal'+@month_count+''')
DROP TABLE avgbal'+@month_count+'

select * into avgbal'+@month_count+'
from [GIANGLH3].[Daily].dbo.avgbal'+@month_count+'

IF EXISTS
(SELECT *
FROM DBO.SYSOBJECTS O
WHERE O.XTYPE IN (''U'') AND O.NAME = ''TBL_AVGBAL_'+@month_count+''')
DROP TABLE TBL_AVGBAL_'+@month_count+'

SELECT CIF,SUM(AVGBAL_QD) AS AVGBAL_QD_'+@month_count+' into TBL_AVGBAL_'+@month_count+'
 FROM AVGBAL'+@month_count+' A
WHERE PRODUCT_GROUP=''1. CURRENT ACCOUNT''
 AND ((A.SEGMENT NOT IN(''KHCN'',''HH'') 
		AND A.BRANCH_CODE collate database_default NOT IN (SELECT BRANCH_CODE FROM PHUONGANH.DBO.BRANCHOFF))
OR (A.SEGMENT IS NULL AND LEFT(A.SECTOR,1)>1))
GROUP BY CIF
')
set @count= @count + 1
end

exec('
ALTER TABLE TBL_AVGBAL_'+@last_month+'
ADD AVGBAL_QD_'+@2_month_ago+' FLOAT, AVGBAL_QD_'+@3_month_ago+' FLOAT,AVGBAL_QD_'+@4_month_ago+' FLOAT')
print('1')
exec('
UPDATE A
SET a.AVGBAL_QD_'+@2_month_ago+'=b.AVGBAL_QD_'+@2_month_ago+'
FROM TBL_AVGBAL_'+@last_month+' A,TBL_AVGBAL_'+@2_month_ago+' B
WHERE A.CIF collate database_default=B.CIF

UPDATE A
SET a.AVGBAL_QD_'+@3_month_ago+'=b.AVGBAL_QD_'+@3_month_ago+'
FROM TBL_AVGBAL_'+@last_month+' A,TBL_AVGBAL_'+@3_month_ago+' B
WHERE A.CIF collate database_default=B.CIF

UPDATE A
SET a.AVGBAL_QD_'+@4_month_ago+'=b.AVGBAL_QD_'+@4_month_ago+'
FROM TBL_AVGBAL_'+@last_month+' A,TBL_AVGBAL_'+@4_month_ago+' B
WHERE A.CIF collate database_default=B.CIF

delete from CURRENT_ACCOUNT_GIAM

insert CURRENT_ACCOUNT_GIAM
SELECT * ,AVGBAL_QD_'+@last_month+'-AVGBAL_QD_'+@2_month_ago+' AS DELTA_4 
,AVGBAL_QD_'+@2_month_ago+'-AVGBAL_QD_'+@3_month_ago+' AS DELTA_3
,AVGBAL_QD_'+@3_month_ago+'-AVGBAL_QD_'+@4_month_ago+' AS DELTA_2
FROM TBL_AVGBAL_'+@last_month+' A
WHERE AVGBAL_QD_'+@2_month_ago+' IS NOT NULL
AND AVGBAL_QD_'+@3_month_ago+' IS NOT NULL
AND AVGBAL_QD_'+@4_month_ago+' IS NOT NULL
AND AVGBAL_QD_'+@last_month+' IS NOT NULL

delete from TBL_ALERT_CURRENT_ACCOUNT
where month < '''+@2_month_ago+'''


insert TBL_ALERT_CURRENT_ACCOUNT
SELECT A.*,B.DAO,B.CUS_NAME,B.INDUSTRY_NAME,B.VPB_INDUSTRY, b.dao_name, b.BRANCH_ID, '''+@last_month+'''
 FROM CURRENT_ACCOUNT_GIAM A
LEFT JOIN TBL_CUSTOMER B
ON A.CIF =B.CIF
WHERE DELTA_2 <0 AND DELTA_3 <0 AND DELTA_4 <0 

delete from TBL_ALERT_CURRENT_ACCOUNT where dao is null
')


update b
set b.date_data = (select MAX_DATE_ENDMONTH from DATE_EM_REPORT)
from TBL_DATE_BUSINESS b
where b.name_table = 'TBL_ALERT_CURRENT_ACCOUNT'

--select * from TBL_ALERT_CURRENT_ACCOUNT order by dao,cif, month